#!/usr/bin/env python
#coding=utf-8
from pysqlite2 import dbapi2 as sqlite
import datetime
import utils
from ex import NoPrice,NoPreDay

def create_table_price():
    con = utils.get_db_connector()
    c = con.cursor()
    create_price = "create table price (id integer primary key,\
                                          date text not null default current_date,\
                                          code text not null,\
                                          high real , low real, open real, close real,adj_close real,\
                                          volume int, \
                                          volume_ratio real, change real,\
                                        unique (date, code));"
    c.execute(create_price)
    con.commit()
    
    c.close()
    con.close()

###
#def insert_prices_one(cursor, data):
#    #data is a dict , which have keys "date, code, open, close, high, low, volume"
#    insert_sql = "insert into prices(date,code, open, close, high, low, volume) values(?,?,?,?,?,?,?)"
#    v = [data['date'], data['code'], data['open'], data['close'], data['high'],data['low'], data['volume']]
#    cursor.excute(insert_sql, v)
###

def mk_insert_sql(data):
    ks = data.keys()
    vs = [":%s"%k for k in ks]    
    kv = [ks , vs]
    
    s = []
    for l in kv:
        str = ','.join(l)
        s.append('(%s)'%str)
    s = (s[0], s[1])
    
    insert_sql = "insert into price%s values%s"%s
    return insert_sql

def insert_price_one(cursor, data):
    s = mk_insert_sql(data)
    #print s 
    cursor.execute(s, data)
        
#n: how many to commit
def insert_price_many(con, data_list, n):
    c = con.cursor()
    i = 0
    for d in data_list:
        insert_price_one(c, d)
        i = i + 1
        if i % n == 0: con.commit()
    con.commit()

def price_tuple2dict(row):
    r = {}
    items = ['id', 'date', 'code', 'high', 'low', 'open', 'close', 'adj_close', 
            'volume', 'volume_ratio', 'change']
    for k, v in zip(items, row):
        if not v: continue
        r[k] = v
    return r

def lookup_price(cursor, code, date):
    c = cursor
    c.execute('select * from price where code=? and date=?', [code, date])
    r = c.fetchone()
    if not r:raise NoPrice(code, date)
    d = price_tuple2dict(r)     
    #print d
    c.close()
    return d

def pre_nday(cursor, code, y,m,d, n):
    origin_day_isostr = utils.get_date_isostr(y,m,d)
    
    sql = "select date from price where code=%s order by date"%code
    c = cursor
    c.execute(sql)
    ds = c.fetchall()
    
    #print ds, origin_day_isostr
    try:
        i = ds.index(( origin_day_isostr,))
    except ValueError,e:
        raise NoPrice(code, origin_day_isostr)
    else:    
        if i < n: raise NoPreDay(code, origin_isodate)

        preday_isostr = ds[i-n][0]
        preday = [int(s) for s in preday_isostr.split('-')]
        return preday

def pre_1day_close(cursor, code, y, m, n):
    d = pre_nday(cursor, code, y, m, n, 1 )
    dstr = utils.get_date_isostr( *d )
    p = lookup_price(cursor, code, dstr)
    return p['close']

def create_table_exchangedays(con):
    sql = "create table exchangedays (id integer primary key,\
                                      code text not null,\
                                      date text not null,\
                                      pre_day_id integer default null);"
    c = con.cursor()
    c.execute(sql)
    
    con.commit()
    c.close()
    
    
def dayslist2db(con, code, days):
    c = con.cursor()
    pre_day_id = None
    for d in days:
        if not pre_day_id:
            sql = "insert into exchangedays(code, date) values(%s, '%s')"%(code, d)
        else:
            sql = "insert into exchangedays(code, date, pre_day_id) values(%s,'%s',%i)"%(code,d,pre_day_id)
        c.execute(sql)
        con.commit()
        c.execute('select last_insert_rowid()')
        pre_day_id = c.fetchone()[0]
    con.commit()
    c.close()